<?php

class Admin_Table_Auction_Lots extends Apk_Db_Table {

    protected $_name = 'auction_lots';
    protected $_primary = 'ID';
    public $_ERROR = "";

    public function GetError() {
        return $this->_ERROR;
    }

    public function getDataList(/*$id_auction,*/ $page = 1, $sort = null, $page_count = null, $filters = array()) {
        $fields = array(
			            'id',
			            'cad_num' => "concat(LPAD(cad_num_koatuu, 10, 0), ':', LPAD(cad_num_zone, 2, 0), ':', LPAD(cad_num_quarter, 3, 0), ':', LPAD(cad_num_parcel, 4, 0))",
			            'area',            
			            'parcel_address' => 'address',
			            'purpose',
                                    'attantion' => 'IF(('.$this->_name . '.id_status > 10 and is_approved != 1 and '.$this->_name . '.id_status != 15) or ('.
                                                          $this->_name.'.is_gone = 1 and '.$this->_name.'.id_status = 2), 1,0)'
			        );

        $sel = $this->getAdapter()->select()
	                ->from($this->_name, $fields)                
		                ->where($this->_name .".is_ghost = ?", 0)
		                ->where($this->_name .".is_removed = ?", 0)
	                ->joinLeft(array('auction'), "auction.id = " . $this->_name . ".id_auction", array('start_date'))
	                ->joinLeft(array('auction_lots_status'), "auction_lots_status.id = ".$this->_name . ".id_status", array('status'=>'name'));
        
    	if (!empty($filters)) {
    		foreach ($filters as $k => $v){
    			switch ($k) {
    				case 'by_cad_num':
    					$cad_num = preg_replace("/:/u", "", $v);
                                        $sel->where("concat(LPad(cad_num_koatuu, 10, 0), LPad(cad_num_zone, 2, 0), LPad(cad_num_quarter, 3, 0), LPad(cad_num_parcel, 4, 0)) like '" . $cad_num . "%'");
    					break;
    				case 'id_auction':
    					if(intval($v)>0) $sel->where("id_auction = ?", $v);
    					break;
    				case 'by_status':
    					if(intval($v)>0) $sel->where($this->_name .".id_status = ?", $v);
    					break;
                                case 'cad_num_koatuu':
                                        $koatuu_offset_mask = $this->KOATUUOffsetMask($v);
                                        if (!empty($koatuu_offset_mask)) {
                                            $sel->where("RPad(RPad(LPad(" . $this->_name . "." . $k . ", 10, 0), $koatuu_offset_mask, 0), 10, 0) = " . $v);
                                        }
                                        break;
                                case 'by_approved':
                                        if (intval($v) >= 0)
                                            $sel->where($this->_name . ".is_approved = ?", intval($v));
                                        break;
                                case 'by_gone':
                                        if (intval($v) >= 0)
                                            $sel->where($this->_name . ".is_gone = ?", intval($v));
                                        break;
    			}
    		}
        }

        $pieces = preg_split("/\s/u", $sort);
        switch ($pieces[0]) {
            case 'id':
                $sel->order($this->_name.".id " . $pieces[1]);
                break;
            case 'cad_num':
                $sel->order("concat(cad_num_koatuu, cad_num_zone, cad_num_quarter, cad_num_parcel) " . $pieces[1]);
                break;
            case 'area':
                $sel->order($sort);
                break;
            case 'status':
                $sel->order("auction_lots_status.name " . $pieces[1]);
                break;
            default:
                $sel->order(array($this->_name.".add_date DESC"));
                break;
        }
        
        return $this->setDecorator("lotDecorator")->getPager($sel, $page, array('perPage' => $page_count));
    }

    public function getDataRow($id) {
        $fields = array(
            'cad_num' => "concat(LPAD(cad_num_koatuu, 10, 0), ':', LPAD(cad_num_zone, 2, 0), ':', LPAD(cad_num_quarter, 3, 0), ':', LPAD(cad_num_parcel, 4, 0))",
            'area',
            'parcel_address' => 'address',
            'purpose',
            'land_auctions_organizer',
            'urban_needs_uses',
            'conditions_of_contract_type',
            'conditions_of_contract_text',
            'start_price',
        	'start_annual_fee',
            'urban_land_conditions',
            'limit_land_development',
            'size_registration_fee',
            'size_of_guarantee_fee',
            'the_order_of_contribution',
            'executor_bank_account',
            'executor_name',
            'executor_address',
            'contact_person_position',
            'contact_person_name',
            'contact_person_phone',
            'add_date' => $this->_name.".add_date",//to_char(lots.add_date, 'dd.mm.yyyy hh24:mi')",
            'modify_date' => $this->_name.".modify_date",//"to_char(lots.modify_date, 'dd.mm.yyyy hh24:mi')"
            'id_status',
            'reject_reason'
        );

        $sel = $this->getAdapter()->select()
                ->from($this->_name, $fields)
                ->joinLeft(array('add_account' => "accounts"), "add_account.id = " . $this->_name . ".add_user_id", array('add_user' => "concat(add_account.last_name, ' ', add_account.first_name, ' ', add_account.patronymic_name)"))
                ->joinLeft(array('modify_account' => "accounts"), "modify_account.id = " . $this->_name . ".modify_user_id", array('modify_user' => "concat(modify_account.last_name, ' ', modify_account.first_name, ' ', modify_account.patronymic_name)"))
                ->where($this->_name . ".id = ?", $id);

        return $this->setDecorator("lotDecorator")->getList($sel)->getRow(0)->toArray();
    }

    public function getLotInfo($id) {
        $fields = array(
        	'id_auction',
            'cad_num' => "concat(LPAD(cad_num_koatuu, 10, 0), ':', LPAD(cad_num_zone, 2, 0), ':', LPAD(cad_num_quarter, 3, 0), ':', LPAD(cad_num_parcel, 4, 0))",
            'area',
            'parcel_address' => 'address',
            'purpose',
            'land_auctions_organizer',
            'urban_needs_uses',
        	'conditions_of_contract_type',
            'conditions_of_contract_text',
            'start_price',
        	'start_annual_fee',
            'urban_land_conditions',
            'limit_land_development',
            'size_registration_fee',
            'size_of_guarantee_fee',
            'the_order_of_contribution',
            /*'executor_bank_account',
            'executor_name',
            'executor_address',
            'contact_person_position',
            'contact_person_name',
            'contact_person_phone',*/
            'add_date'=>$this->_name.'.add_date',
            'modify_date'=>$this->_name.'.modify_date',
            'price','annual_fee','term_of_use',
            'id_status',
            'is_approved',
            'is_gone',
            'reject_reason',
            'id_status_reason', 
            'is_ghost'
        );

        $sel = $this->getAdapter()->select()
                ->from($this->_name, $fields)
                ->joinLeft(array('auction'), "auction.id = " . $this->_name . ".id_auction", array('start_date'))
                ->joinLeft(array('add_account' => "accounts"), "add_account.id = " . $this->_name . ".add_user_id", array('add_user' => "concat(add_account.last_name, ' ', add_account.first_name, ' ', add_account.patronymic_name)"))
                ->joinLeft(array('modify_account' => "accounts"), "modify_account.id = " . $this->_name . ".modify_user_id", array('modify_user' => "concat(add_account.last_name, ' ', add_account.first_name, ' ', add_account.patronymic_name)"))
                ->joinLeft(array('auction_lots_status'), "auction_lots_status.id = ".$this->_name . ".id_status", array('status'=>'name'))
                ->where($this->_name . ".id = ?", $id);

        return $this->setDecorator("lotDecorator")->getList($sel)->getRow(0)->toArray();
    }

    public function getLotFilesCount($lot_id) {
        $sel = $this->getAdapter()->select()
                ->from("auction_attaches", array('files_count' => "count(*)"))
                ->where("is_removed = ?", 0)
                ->where("id_lot = ?", $lot_id);
		
        return $this->getList($sel)->getRow(0)->toArray();
    }
    
    
    //РІС‚РѕСЂРѕР№ РїР°СЂР°РјРµС‚СЂ СѓСЃС‚Р°РЅР°РІР»РёРІР°РµС‚ РЅР°С‡Р°Р»СЊРЅРѕРµ id РґР»СЏ РІС‹Р±РѕСЂРєРё СЃРїРёСЃРєР° РІРѕР·РјРѕР¶РЅС‹С… СЃС‚Р°С‚СѓСЃРѕРІ
    public function GetStatusList($status = null, $start = null) {
        $sel = $this->getAdapter()->select()
                ->from('auction_lots_status', array('id', 'name'))                
                ->order('id');
        $result = $this->getList($sel)->toArray();//->setDecorator('selectDecorator')

        if (!empty($result) && isset($status))
            foreach ($result as $key => $value)
                if ($value['id'] == $status)
                    $result[$key]['__attributes']['active'] = 1;
        return $result;
    }
    
    public function GetApprovedList($status = null) {
        $result = array(
            array("id"=> "0","name" => "Не підтвержено"),
            array("id"=> "1","name" => "Підтвержено")
            
            );

        if (!empty($result) && isset($status))
                foreach ($result as $key => $value)
                    if ($value['id'] == $status)
                        $result[$key]['__attributes']['active'] = 1;
            return $result;
    }
    
    public function GetGoneList($status = null) {
        $result = array(
            array("id"=> "0","name" => "Не пройшла"),
            array("id"=> "1","name" => "Пройшла")
            );

        if (!empty($result) && isset($status))
                foreach ($result as $key => $value)
                    if ($value['id'] == $status)
                        $result[$key]['__attributes']['active'] = 1;
            return $result;
    }
    
    // РїР°СЂР°РјРµС‚СЂ $role СѓСЃС‚Р°РЅР°РІР»РёРІР°РµС‚ РѕРїРµСЂР°С‚РѕСЂР° РґР»СЏ РІС‹Р±РѕСЂРєРё СЃРїРёСЃРєР° РІРѕР·РјРѕР¶РЅС‹С… СЃС‚Р°С‚СѓСЃРѕРІ
    public function GetStatusListForChange($role, $active_status = null) {
        
        if ($active_status != null && $active_status == '') $active_status = 0;
        $toStatus = array('0' => array(                         // 0 - СЂР°Р№Р·РµРј
                                    '0'  => array('1'),
                                    '1'  => array('3','12'),
                                    '2'  => array('11','12','13','14','15'), 
                                    '3'  => array('1','12'),
                                    '11' => array(),
                                    '12' => array(),
                                    '13' => array(),
                                    '14' => array(),
                                    '15' => array('11','12','13','14'),
                                    '16' => array()),
                          '1' => array(                         // 1 -РѕР±Р»Р·РµРј
                                    '0'  => array(),
                                    '1'  => array('2', '3'),
                                    '2'  => array('15'),
                                    '3'  => array(),
                                    '11' => array('16'),
                                    '12' => array('16'),
                                    '13' => array('16'),
                                    '14' => array('16'),
                                    '15' => array(),
                                    '16' => array())
                        );    
        $sel = $this->getAdapter()->select()
                ->from('auction_lots_status', array('id', 'name'))                
                ->order('id');
        //if(isset($status_id)) $sel->where('id > ?',$start);
        $result = $this->getList($sel)->toArray();//->setDecorator('selectDecorator')
        if (!empty($result) && isset($active_status))
            foreach ($result as $key => $value){
                //if ($value['id'] == $active_status)
                if(!in_array($value['id'], $toStatus[$role][$active_status]))
                    $result[$key]['__attributes']['disable'] = 1;
                if ($value['id'] == $active_status)
                    $result[$key]['__attributes']['active'] = 1;
            }

        return $result;
    }

    public function insert(array $data, $user_id) {
        $data['add_user_id'] = $user_id;
        $data['add_date'] = new Zend_Db_Expr("now()");//sysdate
        try {
            parent::insert($data);
            return $this->getAdapter()->lastInsertId($this->_name);
        } catch (Exception $e) {
            return false;
        }
    }

    public function update(array $data, $id, $user_id, $role = null) {
        try {
            unset($data['id']);
            $where = '';
            if(isset($role) && $role == 14){ //AuctionEditor
                if($data['id_status'] == 3 )$where = ' and id_status = 1'; 
                if($data['id_status'] == 11 || $data['id_status'] == 12 ||
                    $data['id_status'] == 13 || $data['id_status'] == 14) $where = ' and (id_status = 2 or id_status = 15)';
            }
            if(isset($role) && $role == 15){ //AuctionCheck
                if($data['id_status'] == 2 )$where = ' and id_status = 1';
                if($data['id_status'] == 15 )$where = ' and id_status = 2';
            }
            
            $data['is_ghost'] = 0;
            $data['modify_user_id'] = $user_id;
            $data['modify_date'] = new Zend_Db_Expr("now()");
            return parent::update($data, 'id = ' . $id . $where);
        } catch (Exception $e) {
        	$this->_ERROR = $e->getMessage();
        	return false;
        }
    }
   /* 
    public function updateByChecker(array $data, $id, $user_id) {
        try {
            $data['is_ghost'] = 0;
            $data['modify_user_id'] = $user_id;
            $data['modify_date'] = new Zend_Db_Expr("now()");
            return parent::update($data, 'id = ' . $id. ' and id_status = 1');
        } catch (Exception $e) {
            return false;
        }
    }
    public function updateByEditor(array $data, $id, $user_id) {
        try {
            $data['is_ghost'] = 0;
            $data['modify_user_id'] = $user_id;
            $data['modify_date'] = new Zend_Db_Expr("now()");
            $res = parent::update($data, 'id = ' . $id. ' and id_status != 1');
            return $res;
        } catch (Exception $e) {
            return false;
        }
    }
    */
    
    public function updateLot(array $data, $id, $user_id) {
        unset($data['id']);
        unset($data['id-lot']);
        
        $data['is_ghost'] = 0;
        return $this->update($data, $id, $user_id);
    }

    public function remove(array $items, $user_id) {
        $table_attaches = new Admin_Table_Auction_Attaches();

        foreach ($items as $id) {
            $data = array();
            $data['modify_user_id'] = $user_id;
            $data['modify_date'] = new Zend_Db_Expr("now()");//sysdate
            $data['is_removed'] = 1;

            if(parent::update($data, "id = " . $id . " and id_status != 2"))//remove only if not published(id_status != 2)
            {
                $ids = $table_attaches->getLotFilesIDs($id);
                $data = array();
                foreach ($ids as $attache_id) {
                    $data[] = $attache_id['id'];
                }
                $table_attaches->remove($data, $user_id);
            }
        }

        return true;
    }
    
    public function checkStatus($items, $user_id, $id_status=1)
    {
    	$notInStatus = array('1'=>'1, 2');
    	foreach($items as $k => $id)
    	{
            parent::update(array('id_status'=>$id_status, 'id_status_reason'=>0, 'modify_user_id'=>$user_id),'id_status not in('.$notInStatus[$id_status].') and id = '.$id);
    	}
    	return true;
    }
            
    public function getAuctionID($lot_id) {
        $sel = $this->getAdapter()->select()
                ->from($this->_name, array('id_auction'))
                ->where("is_removed = ?", 0)
                ->where("id = ?", $lot_id);
        $data = $this->getList($sel)->toArray();
        if (!empty($data))
            $data = $data[0]["id_auction"];
        return $data;
    }    

    public function getAuctionLotsIDs($auction_id) {
        $sel = $this->getAdapter()->select()
                ->from($this->_name, array('id'))
                ->where("is_removed = ?", 0)
                ->where("id_auction = ?", $auction_id);

        return $this->getList($sel)->toArray();
    }
    /*
     * РџРѕРЅР°РґРѕР±РёС‚СЃСЏ РїРѕСЃР»Рµ РѕРїСЂРµРґРµР»РµРЅРёСЏ СЃРїРёСЃРєР° РїСЂРёС‡РёРЅ РґР»СЏ РІРѕР·РІСЂР°С‰РµРЅРёСЏ 
     */ 
    public function getStatusReasons($id_status=3, $id_reason=0){
    	$data = array();
    	$sel = $this->getAdapter()->select()
                ->from('auction_status_reasons')
                ->where("id_status = ?", $id_status);
        if($id_reason>0) $sel->where("id = ?", $id_reason);
        
        $data = $this->getList($sel)->toArray();

        if(!empty($data) && $id_reason>0) $data = $data[0];

        return $data;
    }
    
    protected function lotDecorator($key, $value) {
        switch ($key) {
            case 'add_date':
            case 'modify_date':
        		if($value=="" || $value===null) $result[$key] = '';
                else{
                    $date = new DateTime($value);
                    $result[$key] = $date->format('d.m.Y H:i');
                }
            	break; 
            case 'area':
                if (preg_match("/^\./u", $value)) $result[$key] = "0" . $value;
                else $result[$key] = $value;
                break;
            case 'status':
                if($value == '')
                    $result[$key] = 'Р©РѕР№РЅРѕ СЃС‚РІРѕСЂРµРЅРёР№';
                else $result[$key] = $value;
                break;
            case 'attantion':
                if ($value == 0) $result[$key] = "";
                else $result[$key] = "<img src='/images/icons/attention.gif' alt='Аукціон мав відбутися'/>";
                break;
            default:
                $result[$key] = $value;
                break;
        }

        return $result;
    }

}
